package dbb

import (
	"context"
	"database/sql"
	"fmt"

	"fyne.io/fyne/v2/widget"

	"github.com/gogf/gf/v2/database/gdb"
	"github.com/gogf/gf/v2/util/gconv"
	"github.com/y2h/fyne_poem/menu"
	"github.com/y2h/fyne_poem/util"
)

var Ctx context.Context = context.Background()

func GetDb(dbpath string, msg *widget.Label) *sql.DB {
	if !util.IsExist(dbpath) {
		return nil
	}
	db, err := sql.Open("sqlite3", dbpath)
	if err != nil {
		msg.SetText("GetDb err:" + err.Error())
		return nil
	}
	// defer db.Close()
	return db
}

// GetFields get fields of tblName .
// the same with strings.Split(gstr.Replace(db.Model(tblName).GetFieldsStr(), "`", ""), ",")
func GetFields(db gdb.DB, tblName string) []string {
	if db == nil {
		return []string{}
	}

	tableFields, err := db.Model().TableFields(tblName)
	if err != nil {
		panic(err)
	}
	if len(tableFields) == 0 {
		panic(fmt.Sprintf(`empty table fields for table "%s"`, tblName))
	}
	fieldsArray := make([]string, len(tableFields))
	for k, v := range tableFields {
		fieldsArray[v.Index] = k
	}
	return fieldsArray
}

// GetOne 获取单个数据
// 获取数据条数 "select count(*) from " + tableName
// 获取创建表的sql语句"SELECT sql FROM sqlite_master WHERE tbl_name = '" + tableName + "' AND type = 'table'"
func GetOne(db *sql.DB, stmt string, msg *widget.Label) (result string) {
	row := db.QueryRow(stmt)
	err := row.Err()
	if err != nil {
		msg.SetText("GetOneCol err:" + err.Error())
		return
	}
	row.Scan(&result)
	return
}

// GetOneCol 获取一列数据
// 获取sqlite3数据库中的所有表:
// "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
// CREATE TABLE sqlite_master (
//   type text,
//   name text,
//   tbl_name text,
//   rootpage integer,
//   sql text
// );
// type: table、index、view、trigger
func GetOneCol(db *sql.DB, stmt string, msg *widget.Label) (results []string) {
	rows, err := db.Query(stmt)
	if err != nil {
		msg.SetText("GetOneCol err:" + err.Error())
		return
	}
	defer rows.Close()
	var result string
	for rows.Next() {
		err = rows.Scan(&result)
		if err != nil {
			msg.SetText("GetOneCol err:" + err.Error())
			return
		}
		results = append(results, result)
	}
	err = rows.Err()
	if err != nil {
		msg.SetText("GetOneCol err:" + err.Error())
		return
	}
	return
}

// GetCols 获取sqlite3数据库某个表的所有列名
func GetCols(db *sql.DB, tblName string, msg *widget.Label) (cols []string) {
	sqlStr := "PRAGMA table_info('" + tblName + "') "
	rows, err := db.Query(sqlStr)
	if err != nil {
		msg.SetText(err.Error())
		return
	}

	defer rows.Close()
	var c1, c2, c3, c4, c6 string
	var c5 interface{}
	for rows.Next() {
		err = rows.Scan(&c1, &c2, &c3, &c4, &c5, &c6)
		if err != nil {
			msg.SetText("GetCols err:" + err.Error())
			return
		}
		cols = append(cols, c2)
	}
	err = rows.Err()
	if err != nil {
		msg.SetText(err.Error())
		return
	}
	return
}

// GSqliteTblColsGF1 获取sqlite3数据库某个表的结构信息
func SqliteTblColsGF1(db *sql.DB, tblName string) (
	fields map[string]*gdb.TableField, cols []string) {
	sqlStr := "PRAGMA table_info('" + tblName + "') "
	// SELECT * FROM pragma_table_info('+tblName+')
	menu.Glog.Info(Ctx, []byte(sqlStr))

	rows, err := db.Query(sqlStr)
	if err != nil {
		menu.Glog.Info(Ctx, []byte(err.Error()))
		return
	}
	defer rows.Close()
	var c1 int
	var c2, c3, c6 string
	var c4 bool
	var c5 interface{}
	fields = make(map[string]*gdb.TableField)
	for rows.Next() {
		// [cid name type notnull dflt_value pk]
		err = rows.Scan(&c1, &c2, &c3, &c4, &c5, &c6)
		if err != nil {
			menu.Glog.Write([]byte("GetCols err:" + err.Error()))
			return
		}
		fields[c2] = &gdb.TableField{
			Index:   c1,
			Name:    c2,
			Type:    c3,
			Null:    c4,
			Default: c5,
			Key:     c6,
		}
		cols = append(cols, c2)
	}
	err = rows.Err()
	if err != nil {
		menu.Glog.Error(Ctx, []byte(err.Error()))
		return
	}

	menu.Glog.Info(Ctx, []byte(gconv.String(fields)))
	return
}

// SqliteTblColsGF 获取sqlite3数据库某个表的结构信息
// "PRAGMA table_info('" + tblName + "') "
func SqliteTblColsGF(db gdb.DB, tblName string) (
	fields map[string]*gdb.TableField, cols []string) {
	sqlStr := "PRAGMA table_info('" + tblName + "') "
	// SELECT * FROM pragma_table_info('+tblName+')
	result, err := db.GetAll(db.GetCtx(), sqlStr)
	if err != nil {
		menu.Glog.Error(db.GetCtx(), []byte(sqlStr))
		return
	}
	fields = make(map[string]*gdb.TableField)
	for i, m := range result {
		fields[m["name"].String()] = &gdb.TableField{
			Index:   i,
			Name:    m["name"].String(),
			Type:    m["type"].String(),
			Null:    m["null"].Bool(),
			Key:     m["key"].String(),
			Default: m["default_value"].Val(),
			Comment: m["comment"].String(),
		}
		cols = append(cols, m["name"].String())
	}

	menu.Glog.Info(db.GetCtx(), []byte(gconv.String(fields)))
	return
}

func ColsFromResult(result gdb.Result) (cols []string) {
	for k := range result[0] {
		cols = append(cols, k)
	}
	return
}
